<?php
function libsys_drush_command() {
	$items=[];
	$items['notify-overdue'] = [
		'description' => 'Notify overdue readers.',
		'aliases' => ['lno'],
		'bootstrap' => DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION,
		'category' => 'Libsys'
	];
	return $items;
}

function drush_libsys_notify_overdue() {
	// First let's fetch all the database settings
	$datasource=\Drupal::config('libsys.datasource');
	$keys=['host','port','database','username','password'];
	foreach($keys as $key) {
		if(empty($datasource->get($key))) return;
	}
	
	try {
		$k=[];
		
		// Use php-oci8 rather than pdo_oci, which is faster
		$conn=oci_connect($datasource->get('username'),$datasource->get('password'),
			sprintf("%s:%s/%s", $datasource->get('host'), $datasource->get('port'), $datasource->get('database')),'AL32UTF8'
		);
		if(!$conn) {
			$e=oci_error();
			throw new Exception($e['message'], $e['code']);
		}
		
		// Only invoke if the option is enabled. Notify overdued readers
		if(\Drupal::config('libsys.qyweixin')->get('notify_overdue')) {
			$sql=sprintf("SELECT READER.NAME, LEND_LST.CERT_ID, LEND_LST.LEND_DATE, LEND_LST.NORM_RET_DATE, ".
				"ASCIISTR(MARC.M_TITLE) AS M_TITLE, ASCIISTR(MARC.M_AUTHOR) AS M_AUTHOR, ASCIISTR(MARC.M_PUBLISHER) AS M_PUBLISHER, WEIXIN_USER.WEIXIN_CODE ".
				"FROM LEND_LST INNER JOIN INDI_ACCT ON LEND_LST.PROP_NO=INDI_ACCT.PROP_NO INNER JOIN MARC ON INDI_ACCT.MARC_REC_NO=MARC.MARC_REC_NO ".
				"INNER JOIN READER ON LEND_LST.CERT_ID=READER.CERT_ID ".
				"INNER JOIN WEIXIN_USER ON READER.CERT_ID=WEIXIN_USER.CERT_ID ".
				"INNER JOIN READER_CERT ON READER.CERT_ID=READER_CERT.CERT_ID AND READER_CERT.CERT_FLAG='1' ".
				"WHERE to_date(substr(NORM_RET_DATE, 0, 10), 'yyyy-mm-dd')<sysdate ORDER BY READER.CERT_ID");
			$stmt=oci_parse($conn, $sql);
			if(!$stmt) {
				$e=oci_error($stmt);
				throw new \Exception($e['message'], $e['code']);
			}
			$r=oci_execute($stmt, OCI_DEFAULT);
			if(!$r) {
				$e=oci_error($stmt);
				throw new \Exception($e['message'], $e['code']);
			}
			while($row=oci_fetch_array($stmt)) {
				$date=\DateTime::createFromFormat('Y-m-d', substr($row['LEND_DATE'],0,10));
				$return_date=\DateTime::createFromFormat('Y-m-d', substr($row['NORM_RET_DATE'],0,10));
				$num=date_create()->diff($return_date);
				$format='';
				if($num->y)
					$format.='%y年';
				if($num->m)
					$format.='%m个月';
				if($num->d)
					$format.='%d天';
				if(date_create()->diff($return_date)->days) {
					$k[$row['WEIXIN_CODE']][]=
						t("@name, the book @title @writer @publisher you have borrowed in @date should be returned in @return. Now @num days have passed. Please return it asap.", array(
						'@name'=>$row['NAME'], '@title'=>_libsys_process_orcale_asciistr($row['M_TITLE']),
						'@writer'=>_libsys_process_orcale_asciistr($row['M_AUTHOR']),
						'@publisher'=>_libsys_process_orcale_asciistr($row['M_PUBLISHER']),
						'@date'=>\DateTime::createFromFormat('Y-m-d', substr($row['LEND_DATE'],0,10))->format('Y年n月j日'),
						'@return'=>$return_date->format('Y年n月j日'),
						'@num'=>$num->format($format)
					))->__toString();
				}
			}
		}
			
		// Only invoke if the option is enabled. Notify upcoming readers
		if(\Drupal::config('libsys.qyweixin')->get('notify_upcoming')) {
			$days=explode(',',\Drupal::config('libsys.qyweixin')->get('notify_upcoming_days'));
			foreach($days as $day) {
				$sql=sprintf("SELECT READER.NAME, LEND_LST.CERT_ID, LEND_LST.LEND_DATE, LEND_LST.NORM_RET_DATE, ".
					"ASCIISTR(MARC.M_TITLE) AS M_TITLE, ASCIISTR(MARC.M_AUTHOR) AS M_AUTHOR, ASCIISTR(MARC.M_PUBLISHER) AS M_PUBLISHER, WEIXIN_USER.WEIXIN_CODE ".
					"FROM LEND_LST INNER JOIN INDI_ACCT ON LEND_LST.PROP_NO=INDI_ACCT.PROP_NO INNER JOIN MARC ON INDI_ACCT.MARC_REC_NO=MARC.MARC_REC_NO ".
					"INNER JOIN READER ON LEND_LST.CERT_ID=READER.CERT_ID ".
					"INNER JOIN WEIXIN_USER ON READER.CERT_ID=WEIXIN_USER.CERT_ID ".
					"INNER JOIN READER_CERT ON READER.CERT_ID=READER_CERT.CERT_ID AND READER_CERT.CERT_FLAG='1' ".
					"WHERE to_date(substr(NORM_RET_DATE, 0, 10), 'yyyy-mm-dd')-sysdate>%d AND to_date(substr(NORM_RET_DATE, 0, 10), 'yyyy-mm-dd')-sysdate<%d ORDER BY READER.CERT_ID",
					$day-1, $day);

				$stmt=oci_parse($conn, $sql);
				if(!$stmt) {
					$e=oci_error($stmt);
					throw new \Exception($e['message'], $e['code']);
				}
				$r=oci_execute($stmt, OCI_DEFAULT);
				if(!$r) {
					$e=oci_error($stmt);
					throw new \Exception($e['message'], $e['code']);
				}
				while($row=oci_fetch_array($stmt)) {
					$date=\DateTime::createFromFormat('Y-m-d', substr($row['LEND_DATE'],0,10));
					$return_date=\DateTime::createFromFormat('Y-m-d', substr($row['NORM_RET_DATE'],0,10));
					$k[$row['WEIXIN_CODE']][]=
						t("@name, the book @title @writer @publisher you have borrowed in @date should be returned in @return, only @num days left. Please return it asap.", array(
						'@name'=>$row['NAME'], '@title'=>_libsys_process_orcale_asciistr($row['M_TITLE']),
						'@writer'=>_libsys_process_orcale_asciistr($row['M_AUTHOR']),
						'@publisher'=>_libsys_process_orcale_asciistr($row['M_PUBLISHER']),
						'@date'=>$date->format('Y年n月j日'),
						'@return'=>$return_date->format('Y年n月j日'),
						'@num'=>$day
					))->__toString();
				}
			}
		}
		
		// Only invoke if the option is enabled. Notify arrived reservations
		if(\Drupal::config('libsys.qyweixin')->get('notify_preg_arri')) {
			$sql=sprintf("SELECT PREG_ARRI_LST.CALL_NO_F, PREG_ARRI_LST.PREG_ARRI_DATE, PREG_ARRI_LST.KEEP_END_DATE, PREG_ARRI_LST.CERT_ID, ".
				"ASCIISTR(MARC.M_TITLE) AS M_TITLE, READER.NAME, LOCATION_LST.LOCATION_NAME, WEIXIN_USER.WEIXIN_CODE ".
				"FROM LIBSYS.PREG_ARRI_LST ".
				"INNER JOIN INDI_ACCT ON PREG_ARRI_LST.PROP_NO=INDI_ACCT.PROP_NO ".
				"INNER JOIN MARC ON MARC.MARC_REC_NO=INDI_ACCT.MARC_REC_NO ".
				"INNER JOIN LOCATION_LST ON PREG_ARRI_LST.TAKE_LOCATION=LOCATION_LST.LOCATION ".
				"INNER JOIN WEIXIN_USER ON PREG_ARRI_LST.CERT_ID=WEIXIN_USER.CERT_ID ".
				"INNER JOIN READER ON PREG_ARRI_LST.CERT_ID=READER.CERT_ID ".
				"INNER JOIN READER_CERT ON PREG_ARRI_LST.CERT_ID=READER_CERT.CERT_ID AND READER_CERT.CERT_FLAG='1' ".
				"WHERE substr(PREG_ARRI_DATE, 0, 10)=to_char(sysdate,'yyyy-mm-dd') OR substr(KEEP_END_DATE, 0, 10)=to_char(sysdate+1,'yyyy-mm-dd')"
			);

			$stmt=oci_parse($conn, $sql);
			if(!$stmt) {
				$e=oci_error($stmt);
				throw new \Exception($e['message'], $e['code']);
			}
			$r=oci_execute($stmt, OCI_DEFAULT);
			if(!$r) {
				$e=oci_error($stmt);
				throw new \Exception($e['message'], $e['code']);
			}
			while($row=oci_fetch_array($stmt)) {
				$arri_date=\DateTime::createFromFormat('Y-m-d', substr($row['PREG_ARRI_DATE'],0,10));
				$keep_end_date=\DateTime::createFromFormat('Y-m-d', substr($row['KEEP_END_DATE'],0,10));
				$k[$row['WEIXIN_CODE']][]=
					t("@name, the book @title you have reserved has arrived in @arridate. Please lend it before @keepdate at @location with @callno.", array(
					'@name'=>$row['NAME'], '@title'=>_libsys_process_orcale_asciistr($row['M_TITLE']),
					'@arridate'=>$arri_date->format('Y年n月j日'),
					'@keepdate'=>$keep_end_date->format('Y年n月j日'),
					'@location'=>$row['LOCATION_NAME'],
					'@callno'=>$row['CALL_NO_F']
				))->__toString();
			}
		}
		
		// Only invoke if the option is enabled. Notify somebody who own the reservations
		if(\Drupal::config('libsys.qyweixin')->get('notify_preg_arri')) {
			$sql=sprintf("SELECT LEND_LST.CERT_ID, ASCIISTR(MARC.M_TITLE) AS M_TITLE, READER.NAME, WEIXIN_USER.WEIXIN_CODE ".
				"FROM LEND_LST ".
				"INNER JOIN INDI_ACCT ON LEND_LST.PROP_NO=INDI_ACCT.PROP_NO ".
				"INNER JOIN MARC ON MARC.MARC_REC_NO=INDI_ACCT.MARC_REC_NO ".
				"INNER JOIN READER ON LEND_LST.CERT_ID=READER.CERT_ID ".
				"INNER JOIN READER_CERT ON LEND_LST.CERT_ID=READER_CERT.CERT_ID ".
				"INNER JOIN WEIXIN_USER ON LEND_LST.CERT_ID=WEIXIN_USER.CERT_ID ".
				"WHERE EXISTS(SELECT * FROM PREG_LST WHERE PREG_LST.CALL_NO=INDI_ACCT.CALL_NO AND PREG_LST.LOCATION=INDI_ACCT.LOCATION  ".
				"AND PREG_LST.PREG_FLAG=0 AND SUBSTR(PREG_LST.PREG_DATE,0,10)=to_char(sysdate,'yyyy-mm-dd'))"
			);

			$stmt=oci_parse($conn, $sql);
			if(!$stmt) {
				$e=oci_error($stmt);
				throw new \Exception($e['message'], $e['code']);
			}
			$r=oci_execute($stmt, OCI_DEFAULT);
			if(!$r) {
				$e=oci_error($stmt);
				throw new \Exception($e['message'], $e['code']);
			}
			while($row=oci_fetch_array($stmt)) {
				$arri_date=\DateTime::createFromFormat('Y-m-d', substr($row['PREG_ARRI_DATE'],0,10));
				$keep_end_date=\DateTime::createFromFormat('Y-m-d', substr($row['KEEP_END_DATE'],0,10));
				$k[$row['WEIXIN_CODE']][]=
					t("@name, the book @title you have borrowed has been reserved by somebody else today. Please return it immediately after using.", array(
					'@name'=>$row['NAME'], '@title'=>_libsys_process_orcale_asciistr($row['M_TITLE'])
				))->__toString();
			}
		}
		
		$queue = \Drupal::queue('libsys.overdue');
		foreach($k as $reader=>$data) {
			$r=new stdClass();
			$r->readerid=(string)$reader;
			$r->data=$data;
			$queue->createItem($r);
		}

		$num=$queue->numberOfItems();
		if($num) {
			$r=new stdClass();
			$r->readerid='oWoh9wuUktMf24YKiHSx6zcOJAqM';
			$r->data=t('@num people about to notify.', ['@num'=>$num])->__toString();
			$queue->createItem($r);
		}

		drush_queue_run('libsys.overdue');
	} catch (\Exception $e) {
		\Drupal::logger('libsys')->error(
			'Error occured when accessing libsys database: %errcode %errmsg',
			['%errcode'=>$e->getCode(), '%errmsg'=>$e->getMessage()]
		);
	} finally {
		if(!empty($stmt))
			oci_free_statement($stmt);
		if(!empty($conn))
			oci_close($conn);
	}
	// End
}
?>

